package com.bjb.vr.common.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bjb.vr.common.constant.CanalConstant;

import java.util.Iterator;
import java.util.Map;

/**
 * @description:
 * @author: hujingbo
 * @create: 2022-08-16 16:47
 **/
public class StatementUtil {

    /**
     * 封装插入语句
     *
     * @param object
     * @return
     */
    public static String saveInsertSql(JSONObject object) {
        JSONArray dataArray = object.getJSONArray(CanalConstant.DATA);
        JSONObject params = dataArray.getJSONObject(0);
        JSONArray pkArrays = object.getJSONArray(CanalConstant.PK_NAMES);
        StringBuilder builder = new StringBuilder();
        // 排除不需要的属性
        excludePkNames(dataArray, pkArrays);
        // 拼接属性
        builder.append("INSERT INTO ").append("`").append(object.get(CanalConstant.DB)).append("`").append(".").append("`").append(object.get(CanalConstant.TABLE)).append("`").append(" ( ");
        int size = params.entrySet().size();
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            size--;
            builder.append("`").append(entry.getKey()).append("`");
            if (size != 0) {
                builder.append(", ");
            }
        }
        builder.append(")").append(" VALUES ");
        // 拼接values
        int arraySize = dataArray.size();
        for (int i = 0; i < dataArray.size(); i++) {
            arraySize--;
            builder.append("(");
            JSONObject jsonObject = dataArray.getJSONObject(i);
            int objSize = jsonObject.size();
            for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
                objSize--;
                if (entry.getValue() != null) {
                    builder.append("'").append(entry.getValue()).append("'");
                } else {
                    builder.append(entry.getValue());
                }
                if (objSize != 0) {
                    builder.append(", ");
                }
            }
            if (arraySize != 0) {
                builder.append("),");
            } else {
                builder.append(");");
            }
        }
        return builder.toString();
    }

    /**
     * 排除主键
     *
     * @param params
     * @param dataArray
     * @param pkArrays
     */
    private static void excludePkNames(JSONArray dataArray, JSONArray pkArrays) {
        for (int i = 0; i < pkArrays.size(); i++) {
            for (int j = 0; j < dataArray.size(); j++) {
                JSONObject jsonObject = dataArray.getJSONObject(j);
                // 删除属性
                Iterator<Map.Entry<String, Object>> entryIterator = jsonObject.entrySet().iterator();
                while (entryIterator.hasNext()) {
                    Map.Entry<String, Object> objectEntry = entryIterator.next();
                    if (pkArrays.contains(objectEntry.getKey())) {
                        entryIterator.remove();
                    }
                }
            }
        }
    }

    /**
     * 封装更新语句
     *
     * @param object
     * @return
     */
    public static String saveUpdateSql(JSONObject object) {
        JSONArray dataArray = (JSONArray) object.get(CanalConstant.DATA);
        JSONArray oldArray = (JSONArray) object.get(CanalConstant.OLD);
        // 1、拼接SQL
        JSONObject paramsData = dataArray.getJSONObject(0);
        JSONObject params = oldArray.getJSONObject(0);
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE ").append("`").append(object.get(CanalConstant.DB)).append("`").append(".").append("`").append(object.get(CanalConstant.TABLE)).append("`").append(" SET ");
        int size = params.entrySet().size();
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            size--;
            builder.append("`").append(entry.getKey()).append("`").append(" = ");
            if (paramsData.get(entry.getKey()) != null) {
                builder.append("'").append(paramsData.get(entry.getKey())).append("'");
            } else {
                builder.append(paramsData.get(entry.getKey()));
            }
            if (size != 0) {
                builder.append(", ");
            }
        }
        // 2、封装数据ID
        builder.append(" WHERE ").append("`").append(CanalConstant.DATA_ID).append("`").append(" IN ").append("(");
        for (int i = 0; i < dataArray.size(); i++) {
            String dataId = (String) dataArray.getJSONObject(i).get(CanalConstant.DATA_ID);
            builder.append("'").append(dataId).append("'");
            if (i != dataArray.size() - 1) {
                builder.append(",");
            } else {
                builder.append(");");
            }
        }
        return builder.toString();
    }

    /**
     * 封装删除语句
     *
     * @param object
     * @return
     */
    public static String saveDeleteSql(JSONObject object) {
        JSONArray dataArray = object.getJSONArray(CanalConstant.DATA);
        // 1、拼接SQL
        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM ").append("`").append(object.get(CanalConstant.DB)).append("`").append(".").append("`").append(object.get(CanalConstant.TABLE)).append("`");

        // 2、封装数据ID
        builder.append(" WHERE ").append("`").append(CanalConstant.DATA_ID).append("`").append(" IN ").append("(");
        for (int i = 0; i < dataArray.size(); i++) {
            String dataId = (String) dataArray.getJSONObject(i).get(CanalConstant.DATA_ID);
            builder.append("'").append(dataId).append("'");
            if (i != dataArray.size() - 1) {
                builder.append(",");
            } else {
                builder.append(");");
            }
        }
        return builder.toString();
    }
}
